﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Reactive.Linq;
using System.Reactive.Subjects;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using documentRdr.Properties;
using OfficeOpenXml;
using OfficeOpenXml.Style.XmlAccess;

namespace documentRdr
{
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();

            Closing += (s, a) => Settings.Default.Save();

            offerFilePathToolStripMenuItem.Click += (s, a) =>
                                                        {
                                                            var openDlg = new OpenFileDialog
                                                            {
                                                                Filter = Resources.xlsxFilter
                                                            };
                                                            if (openDlg.ShowDialog() != DialogResult.OK)
                                                                return;
                                                            Settings.Default.OfferFilePath = openDlg.FileName;
                                                            Settings.Default.Save();
                                                            _filesPathSet.OnNext(new uint());
                                                        };
            marketFilePathToolStripMenuItem.Click += (s, a) =>
                                                        {
                                                            var openDlg = new OpenFileDialog
                                                            {
                                                                Filter = Resources.xlsxFilter
                                                            };
                                                            if (openDlg.ShowDialog() != DialogResult.OK)
                                                                return;
                                                            Settings.Default.MarketFilePath = openDlg.FileName;
                                                            Settings.Default.Save();
                                                            _filesPathSet.OnNext(new uint());
                                                        };
            FoodToolStripButton.Enabled = !string.IsNullOrEmpty(Settings.Default.OfferFilePath) &&
                                          !string.IsNullOrEmpty(Settings.Default.MarketFilePath);
            ShopToolStripButton.Enabled = !string.IsNullOrEmpty(Settings.Default.OfferFilePath) &&
                                          !string.IsNullOrEmpty(Settings.Default.MarketFilePath);
            OfficeToolStripButton.Enabled = !string.IsNullOrEmpty(Settings.Default.OfferFilePath) &&
                                          !string.IsNullOrEmpty(Settings.Default.MarketFilePath);
            DiscoToolStripButton.Enabled = !string.IsNullOrEmpty(Settings.Default.OfferFilePath) &&
                                          !string.IsNullOrEmpty(Settings.Default.MarketFilePath);
            HouseKeepingToolStripButton.Enabled = !string.IsNullOrEmpty(Settings.Default.OfferFilePath) &&
                                          !string.IsNullOrEmpty(Settings.Default.MarketFilePath);
            FactoryToolStripButton.Enabled = !string.IsNullOrEmpty(Settings.Default.OfferFilePath) &&
                                          !string.IsNullOrEmpty(Settings.Default.MarketFilePath);
            AllToolStripButton.Enabled = !string.IsNullOrEmpty(Settings.Default.OfferFilePath) &&
                                          !string.IsNullOrEmpty(Settings.Default.MarketFilePath);
            BeautyToolStripButton.Enabled = !string.IsNullOrEmpty(Settings.Default.OfferFilePath) &&
                                          !string.IsNullOrEmpty(Settings.Default.MarketFilePath);

            _filesPathSet
                .Where(_ => !string.IsNullOrEmpty(Settings.Default.OfferFilePath) && !string.IsNullOrEmpty(Settings.Default.MarketFilePath))
                .Subscribe(_ =>
                               {
                                   FoodToolStripButton.Enabled = true;
                                   ShopToolStripButton.Enabled = true;
                                   OfficeToolStripButton.Enabled = true;
                                   DiscoToolStripButton.Enabled = true;
                                   HouseKeepingToolStripButton.Enabled = true;
                                   FactoryToolStripButton.Enabled = true;
                                   AllToolStripButton.Enabled = true;
                                   BeautyToolStripButton.Enabled = true;
                               });


            Observable.FromEventPattern<EventArgs>(AllToolStripButton, "Click")
                .Where(_ => !OfferRadioButton.Checked)
                .Subscribe(_ =>
                    new ResultForm(() =>
                               {
                                   using (var package = new ExcelPackage(new FileInfo(Settings.Default.MarketFilePath)
                                       .Open(FileMode.Open, FileAccess.Read, FileShare.ReadWrite)))
                                   {
                                       var sheet = package.Workbook.Worksheets["ТБК"];
                                       return
                                           sheet.Cells["B:B"]
                                           .Where(cell => cell.Value != null && cell.Style.Fill.BackgroundColor.Rgb != "FFFF0000" && DataProcessing.NumberRegex.Match(cell.Address).Value != 1.ToString())
                                           .Where(cell => cell.Offset(0, 11).Value == null || DataProcessing.IsSquareMatches(cell.Offset(0, 11).IfNotNull(c => c.Value).ToString(), (int)squareNumericUpDown.Value, (int)variationNumericUpDown.Value))
                                           .OrderBy(cell => cell.Offset(0, 10).IfNotNull(c => c.Value))
                                           .Select(cell => new { cell, District = DataProcessing.GetDistrict(cell) })
                                           .Where(cellInfo =>
                                               (SAOcheckBox.Checked ? cellInfo.District == "САО" : false)
                                               ||
                                               (SVAOcheckBox.Checked ? cellInfo.District == "СВАО" : false)
                                               ||
                                               (UZAOcheckBox.Checked ? cellInfo.District == "ЮЗАО" : false)
                                               ||
                                               (UVAOcheckBox.Checked ? cellInfo.District == "ЮВАО" : false)
                                               ||
                                               (UAOcheckBox.Checked ? cellInfo.District == "ЮАО" : false)
                                               ||
                                               (VAOcheckBox.Checked ? cellInfo.District == "ВАО" : false)
                                               ||
                                               (ZAOcheckBox.Checked ? cellInfo.District == "ЗАО" : false)
                                               ||
                                               (SZAOcheckBox.Checked ? cellInfo.District == "СЗАО" : false)
                                               ||
                                               (CAOcheckBox.Checked ? cellInfo.District == "ЦАО" : false)
                                               ||
                                               cellInfo.District == null
                                               )
                                           .Select(cellInfo => new
                                           {
                                               Name = cellInfo.cell.IfNotNull(c => c.Value),
                                               cellInfo.District,
                                               Metro = cellInfo.cell.Offset(0, 10).IfNotNull(c => c.Value),
                                               Area = cellInfo.cell.Offset(0, 11).IfNotNull(c => c.Value),
                                               Power = cellInfo.cell.Offset(0, 12).IfNotNull(c => c.Value),
                                               Function = cellInfo.cell.Offset(0, 13).IfNotNull(c => c.Value),
                                               Remarks = cellInfo.cell.Offset(0, 14).IfNotNull(c => c.Value),
                                           })
                                               .ToList();
                                   }
                               }).Show());

            Observable.FromEventPattern<EventArgs>(AllToolStripButton, "Click")
                .Where(_ => OfferRadioButton.Checked)
                .Subscribe(_ => new ResultForm(
                                                      ()=>
                                                          {
                                                              using (var package = new ExcelPackage(new FileInfo(Settings.Default.OfferFilePath)
                                                                                                        .Open(FileMode.Open, FileAccess.Read, FileShare.ReadWrite)))
                                                              {
                                                                  var sheet = package.Workbook.Worksheets["ТБК"];
                                                                  return
                                                                      sheet.Cells["C:C"]
                                                                      .Where(cell => cell.Value != null && cell.Style.Fill.BackgroundColor.Rgb != "FFFF0000" && DataProcessing.NumberRegex.Match(cell.Address).Value != 1.ToString())
                                                                      .Where(cell => cell.Offset(0, 11).Value == null || DataProcessing.IsSquareMatches(cell.Offset(0, 11).IfNotNull(c => c.Value).ToString(), (int)squareNumericUpDown.Value, (int)variationNumericUpDown.Value))
                                                                      .OrderBy(cell => cell.Offset(0, 10).IfNotNull(c => c.Value))
                                                                      .Select(cell => new { cell, District = DataProcessing.GetDistrict(cell) })
                                                                      .Where(cellInfo =>
                                                                          (SAOcheckBox.Checked ? cellInfo.District == "САО" : false)
                                                                          ||
                                                                          (SVAOcheckBox.Checked ? cellInfo.District == "СВАО" : false)
                                                                          ||
                                                                          (UZAOcheckBox.Checked ? cellInfo.District == "ЮЗАО" : false)
                                                                          ||
                                                                          (UVAOcheckBox.Checked ? cellInfo.District == "ЮВАО" : false)
                                                                          ||
                                                                          (UAOcheckBox.Checked ? cellInfo.District == "ЮАО" : false)
                                                                          ||
                                                                          (VAOcheckBox.Checked ? cellInfo.District == "ВАО" : false)
                                                                          ||
                                                                          (ZAOcheckBox.Checked ? cellInfo.District == "ЗАО" : false)
                                                                          ||
                                                                          (SZAOcheckBox.Checked ? cellInfo.District == "СЗАО" : false)
                                                                          ||
                                                                          (CAOcheckBox.Checked ? cellInfo.District == "ЦАО" : false)
                                                                          ||
                                                                          cellInfo.District == null
                                                                          )
                                                                      .Select(cellInfo => new
                                                                          {
                                                                              Name = cellInfo.cell.Offset(0, -1).IfNotNull(c => c.Value) ?? cellInfo.cell.Offset(0, -2).IfNotNull(c => c.Value),
                                                                              Contact = cellInfo.cell.IfNotNull(c => c.Value),
                                                                              cellInfo.District,
                                                                              Metro = cellInfo.cell.Offset(0, 10).IfNotNull(c => c.Value),
                                                                              Area = cellInfo.cell.Offset(0, 11).IfNotNull(c => c.Value),
                                                                              Function = cellInfo.cell.Offset(0, 13).IfNotNull(c => c.Value),
                                                                              Remarks = cellInfo.cell.Offset(0, 14).IfNotNull(c => c.Value),
                                                                          })
                                                                          .ToList();
                                                              }
                                                          }
                                                      ).Show());
            Observable.FromEventPattern<EventArgs>(FoodToolStripButton, "Click")
                .Where(_ => OfferRadioButton.Checked)
                .Subscribe(_  => new ResultForm(
                                                      () =>
                                                      {
                                                          using (var package = new ExcelPackage(new FileInfo(OfferRadioButton.Checked ? Settings.Default.OfferFilePath : Settings.Default.MarketFilePath)
                                                                                                    .Open(FileMode.Open, FileAccess.Read, FileShare.ReadWrite)))
                                                          {
                                                              var sheet = package.Workbook.Worksheets["ТБК"];
                                                              return
                                                                  sheet.Cells["C:C"]
                                                                  .Where(cell => cell.Value != null && cell.Style.Fill.BackgroundColor.Rgb != "FFFF0000" && DataProcessing.NumberRegex.Match(cell.Address).Value != 1.ToString())
                                                                  .Where(cell => cell.Offset(0, 13).Value == null || DataProcessing.FoodRegex.Match(cell.Offset(0, 13).IfNotNull(c => c.Value).ToString()).Success)
                                                                  .Where(cell => cell.Offset(0, 11).Value == null || DataProcessing.IsSquareMatches(cell.Offset(0, 11).IfNotNull(c => c.Value).ToString(),(int)squareNumericUpDown.Value,(int)variationNumericUpDown.Value))
                                                                  .OrderBy(cell => cell.Offset(0, 10).IfNotNull(c => c.Value))
                                                                  .Select(cell => new{cell,District = DataProcessing.GetDistrict(cell)})
                                                                  .Where(cellInfo =>
                                                                      (SAOcheckBox.Checked ? cellInfo.District == "САО" : false)
                                                                      ||
                                                                      (SVAOcheckBox.Checked ? cellInfo.District == "СВАО" : false)
                                                                      ||
                                                                      (UZAOcheckBox.Checked ? cellInfo.District == "ЮЗАО" : false)
                                                                      ||
                                                                      (UVAOcheckBox.Checked ? cellInfo.District == "ЮВАО" : false)
                                                                      ||
                                                                      (UAOcheckBox.Checked ? cellInfo.District == "ЮАО" : false)
                                                                      ||
                                                                      (VAOcheckBox.Checked ? cellInfo.District == "ВАО" : false)
                                                                      ||
                                                                      (ZAOcheckBox.Checked ? cellInfo.District == "ЗАО" : false)
                                                                      ||
                                                                      (SZAOcheckBox.Checked ? cellInfo.District == "СЗАО" : false)
                                                                      ||
                                                                      (CAOcheckBox.Checked ? cellInfo.District == "ЦАО" : false)
                                                                      ||
                                                                      cellInfo.District == null
                                                                      )
                                                                      .Select(cellInfo => new
                                                                        {
                                                                            Name = cellInfo.cell.Offset(0, -1).IfNotNull(c => c.Value) ?? cellInfo.cell.Offset(0, -2).IfNotNull(c => c.Value),
                                                                            Contact = cellInfo.cell.IfNotNull(c => c.Value),
                                                                            cellInfo.District,
                                                                            Metro = cellInfo.cell.Offset(0, 10).IfNotNull(c => c.Value),
                                                                            Area = cellInfo.cell.Offset(0, 11).IfNotNull(c => c.Value),
                                                                            Function = cellInfo.cell.Offset(0, 13).IfNotNull(c => c.Value),
                                                                            Remarks = cellInfo.cell.Offset(0, 14).IfNotNull(c => c.Value),
                                                                        })
                                                                      .ToList();
                                                          }
                                                      }
                                                      ).Show());

            Observable.FromEventPattern<EventArgs>(FoodToolStripButton, "Click")
                .Where(_ => !OfferRadioButton.Checked)
                .Subscribe(_ =>
                    new ResultForm(() =>
                    {
                        using (var package = new ExcelPackage(new FileInfo(Settings.Default.MarketFilePath)
                            .Open(FileMode.Open, FileAccess.Read, FileShare.ReadWrite)))
                        {
                            var sheet = package.Workbook.Worksheets["ТБК"];
                            return
                                sheet.Cells["B:B"]
                                .Where(cell => cell.Value != null && cell.Style.Fill.BackgroundColor.Rgb != "FFFF0000" && DataProcessing.NumberRegex.Match(cell.Address).Value != 1.ToString())
                                .Where(cell => cell.Offset(0, 13).Value == null || DataProcessing.FoodRegex.Match(cell.Offset(0, 13).IfNotNull(c => c.Value).ToString()).Success)
                                .Where(cell => cell.Offset(0, 11).Value == null || DataProcessing.IsSquareMatches(cell.Offset(0, 11).IfNotNull(c => c.Value).ToString(), (int)squareNumericUpDown.Value, (int)variationNumericUpDown.Value))
                                .OrderBy(cell => cell.Offset(0, 10).IfNotNull(c => c.Value))
                                .Select(cell => new { cell, District = DataProcessing.GetDistrict(cell) })
                                .Where(cellInfo =>
                                    (SAOcheckBox.Checked ? cellInfo.District == "САО" : false)
                                    ||
                                    (SVAOcheckBox.Checked ? cellInfo.District == "СВАО" : false)
                                    ||
                                    (UZAOcheckBox.Checked ? cellInfo.District == "ЮЗАО" : false)
                                    ||
                                    (UVAOcheckBox.Checked ? cellInfo.District == "ЮВАО" : false)
                                    ||
                                    (UAOcheckBox.Checked ? cellInfo.District == "ЮАО" : false)
                                    ||
                                    (VAOcheckBox.Checked ? cellInfo.District == "ВАО" : false)
                                    ||
                                    (ZAOcheckBox.Checked ? cellInfo.District == "ЗАО" : false)
                                    ||
                                    (SZAOcheckBox.Checked ? cellInfo.District == "СЗАО" : false)
                                    ||
                                    (CAOcheckBox.Checked ? cellInfo.District == "ЦАО" : false)
                                    ||
                                    cellInfo.District == null
                                    )
                                .Select(cellInfo => new
                                {
                                    Name = cellInfo.cell.IfNotNull(c => c.Value),
                                    cellInfo.District,
                                    Metro = cellInfo.cell.Offset(0, 10).IfNotNull(c => c.Value),
                                    Area = cellInfo.cell.Offset(0, 11).IfNotNull(c => c.Value),
                                    Power = cellInfo.cell.Offset(0, 12).IfNotNull(c => c.Value),
                                    Function = cellInfo.cell.Offset(0, 13).IfNotNull(c => c.Value),
                                    Remarks = cellInfo.cell.Offset(0, 14).IfNotNull(c => c.Value),
                                })
                                    .ToList();
                        }
                    }).Show());
            Observable.FromEventPattern<EventArgs>(ShopToolStripButton, "Click")
                .Where(_ => OfferRadioButton.Checked)
                .Subscribe(_ => new ResultForm(
                                                      () =>
                                                      {
                                                          using (var package = new ExcelPackage(new FileInfo(OfferRadioButton.Checked ? Settings.Default.OfferFilePath : Settings.Default.MarketFilePath)
                                                                                                    .Open(FileMode.Open, FileAccess.Read, FileShare.ReadWrite)))
                                                          {
                                                              var sheet = package.Workbook.Worksheets["ТБК"];
                                                              return
                                                                  sheet.Cells["C:C"]
                                                                  .Where(cell => cell.Value != null && cell.Style.Fill.BackgroundColor.Rgb != "FFFF0000" && DataProcessing.NumberRegex.Match(cell.Address).Value != 1.ToString())
                                                                  .Where(cell => cell.Offset(0, 13).Value == null || DataProcessing.ShopRegex.Match(cell.Offset(0, 13).IfNotNull(c => c.Value).ToString()).Success)
                                                                  .Where(cell => cell.Offset(0, 11).Value == null || DataProcessing.IsSquareMatches(cell.Offset(0, 11).IfNotNull(c => c.Value).ToString(), (int)squareNumericUpDown.Value, (int)variationNumericUpDown.Value))
                                                                  .OrderBy(cell => cell.Offset(0, 10).IfNotNull(c => c.Value))
                                                                  .Select(cell => new { cell, District = DataProcessing.GetDistrict(cell) })
                                                                  .Where(cellInfo =>
                                                                      (SAOcheckBox.Checked ? cellInfo.District == "САО" : false)
                                                                      ||
                                                                      (SVAOcheckBox.Checked ? cellInfo.District == "СВАО" : false)
                                                                      ||
                                                                      (UZAOcheckBox.Checked ? cellInfo.District == "ЮЗАО" : false)
                                                                      ||
                                                                      (UVAOcheckBox.Checked ? cellInfo.District == "ЮВАО" : false)
                                                                      ||
                                                                      (UAOcheckBox.Checked ? cellInfo.District == "ЮАО" : false)
                                                                      ||
                                                                      (VAOcheckBox.Checked ? cellInfo.District == "ВАО" : false)
                                                                      ||
                                                                      (ZAOcheckBox.Checked ? cellInfo.District == "ЗАО" : false)
                                                                      ||
                                                                      (SZAOcheckBox.Checked ? cellInfo.District == "СЗАО" : false)
                                                                      ||
                                                                      (CAOcheckBox.Checked ? cellInfo.District == "ЦАО" : false)
                                                                      ||
                                                                      cellInfo.District == null
                                                                      )
                                                                      .Select(cellInfo => new
                                                                      {
                                                                          Name = cellInfo.cell.Offset(0, -1).IfNotNull(c => c.Value) ?? cellInfo.cell.Offset(0, -2).IfNotNull(c => c.Value),
                                                                          Contact = cellInfo.cell.IfNotNull(c => c.Value),
                                                                          cellInfo.District,
                                                                          Metro = cellInfo.cell.Offset(0, 10).IfNotNull(c => c.Value),
                                                                          Area = cellInfo.cell.Offset(0, 11).IfNotNull(c => c.Value),
                                                                          Function = cellInfo.cell.Offset(0, 13).IfNotNull(c => c.Value),
                                                                          Remarks = cellInfo.cell.Offset(0, 14).IfNotNull(c => c.Value),
                                                                      })
                                                                      .ToList();
                                                          }
                                                      }
                                                      ).Show());
            Observable.FromEventPattern<EventArgs>(ShopToolStripButton, "Click")
                .Where(_ => !OfferRadioButton.Checked)
                .Subscribe(_ =>
                    new ResultForm(() =>
                    {
                        using (var package = new ExcelPackage(new FileInfo(Settings.Default.MarketFilePath)
                            .Open(FileMode.Open, FileAccess.Read, FileShare.ReadWrite)))
                        {
                            var sheet = package.Workbook.Worksheets["ТБК"];
                            return
                                sheet.Cells["B:B"]
                                .Where(cell => cell.Value != null && cell.Style.Fill.BackgroundColor.Rgb != "FFFF0000" && DataProcessing.NumberRegex.Match(cell.Address).Value != 1.ToString())
                                .Where(cell => cell.Offset(0, 13).Value == null || DataProcessing.ShopRegex.Match(cell.Offset(0, 13).IfNotNull(c => c.Value).ToString()).Success)
                                .Where(cell => cell.Offset(0, 11).Value == null || DataProcessing.IsSquareMatches(cell.Offset(0, 11).IfNotNull(c => c.Value).ToString(), (int)squareNumericUpDown.Value, (int)variationNumericUpDown.Value))
                                .OrderBy(cell => cell.Offset(0, 10).IfNotNull(c => c.Value))
                                .Select(cell => new { cell, District = DataProcessing.GetDistrict(cell) })
                                .Where(cellInfo =>
                                    (SAOcheckBox.Checked ? cellInfo.District == "САО" : false)
                                    ||
                                    (SVAOcheckBox.Checked ? cellInfo.District == "СВАО" : false)
                                    ||
                                    (UZAOcheckBox.Checked ? cellInfo.District == "ЮЗАО" : false)
                                    ||
                                    (UVAOcheckBox.Checked ? cellInfo.District == "ЮВАО" : false)
                                    ||
                                    (UAOcheckBox.Checked ? cellInfo.District == "ЮАО" : false)
                                    ||
                                    (VAOcheckBox.Checked ? cellInfo.District == "ВАО" : false)
                                    ||
                                    (ZAOcheckBox.Checked ? cellInfo.District == "ЗАО" : false)
                                    ||
                                    (SZAOcheckBox.Checked ? cellInfo.District == "СЗАО" : false)
                                    ||
                                    (CAOcheckBox.Checked ? cellInfo.District == "ЦАО" : false)
                                    ||
                                    cellInfo.District == null
                                    )
                                .Select(cellInfo => new
                                {
                                    Name = cellInfo.cell.IfNotNull(c => c.Value),
                                    cellInfo.District,
                                    Metro = cellInfo.cell.Offset(0, 10).IfNotNull(c => c.Value),
                                    Area = cellInfo.cell.Offset(0, 11).IfNotNull(c => c.Value),
                                    Power = cellInfo.cell.Offset(0, 12).IfNotNull(c => c.Value),
                                    Function = cellInfo.cell.Offset(0, 13).IfNotNull(c => c.Value),
                                    Remarks = cellInfo.cell.Offset(0, 14).IfNotNull(c => c.Value),
                                })
                                    .ToList();
                        }
                    }).Show());
            Observable.FromEventPattern<EventArgs>(OfficeToolStripButton, "Click")
                .Where(_ => OfferRadioButton.Checked)
                .Subscribe(_ => new ResultForm(
                                                      () =>
                                                      {
                                                          using (var package = new ExcelPackage(new FileInfo(OfferRadioButton.Checked ? Settings.Default.OfferFilePath : Settings.Default.MarketFilePath)
                                                                                                    .Open(FileMode.Open, FileAccess.Read, FileShare.ReadWrite)))
                                                          {
                                                              var sheet = package.Workbook.Worksheets["ТБК"];
                                                              return
                                                                  sheet.Cells["C:C"]
                                                                  .Where(cell => cell.Value != null && cell.Style.Fill.BackgroundColor.Rgb != "FFFF0000" && DataProcessing.NumberRegex.Match(cell.Address).Value != 1.ToString())
                                                                  .Where(cell => cell.Offset(0, 13).Value == null || DataProcessing.OfficeRegex.Match(cell.Offset(0, 13).IfNotNull(c => c.Value).ToString()).Success)
                                                                  .Where(cell => cell.Offset(0, 11).Value == null || DataProcessing.IsSquareMatches(cell.Offset(0, 11).IfNotNull(c => c.Value).ToString(), (int)squareNumericUpDown.Value, (int)variationNumericUpDown.Value))
                                                                  .OrderBy(cell => cell.Offset(0, 10).IfNotNull(c => c.Value))
                                                                      .Select(cell => new { cell, District = DataProcessing.GetDistrict(cell) })
                                                                  .Where(cellInfo =>
                                                                      (SAOcheckBox.Checked ? cellInfo.District == "САО" : false)
                                                                      ||
                                                                      (SVAOcheckBox.Checked ? cellInfo.District == "СВАО" : false)
                                                                      ||
                                                                      (UZAOcheckBox.Checked ? cellInfo.District == "ЮЗАО" : false)
                                                                      ||
                                                                      (UVAOcheckBox.Checked ? cellInfo.District == "ЮВАО" : false)
                                                                      ||
                                                                      (UAOcheckBox.Checked ? cellInfo.District == "ЮАО" : false)
                                                                      ||
                                                                      (VAOcheckBox.Checked ? cellInfo.District == "ВАО" : false)
                                                                      ||
                                                                      (ZAOcheckBox.Checked ? cellInfo.District == "ЗАО" : false)
                                                                      ||
                                                                      (SZAOcheckBox.Checked ? cellInfo.District == "СЗАО" : false)
                                                                      ||
                                                                      (CAOcheckBox.Checked ? cellInfo.District == "ЦАО" : false)
                                                                      ||
                                                                      cellInfo.District == null
                                                                      )
                                                                      .Select(cellInfo => new
                                                                      {
                                                                          Name = cellInfo.cell.Offset(0, -1).IfNotNull(c => c.Value) ?? cellInfo.cell.Offset(0, -2).IfNotNull(c => c.Value),
                                                                          Contact = cellInfo.cell.IfNotNull(c => c.Value),
                                                                          cellInfo.District,
                                                                          Metro = cellInfo.cell.Offset(0, 10).IfNotNull(c => c.Value),
                                                                          Area = cellInfo.cell.Offset(0, 11).IfNotNull(c => c.Value),
                                                                          Function = cellInfo.cell.Offset(0, 13).IfNotNull(c => c.Value),
                                                                          Remarks = cellInfo.cell.Offset(0, 14).IfNotNull(c => c.Value),
                                                                      })
                                                                      .ToList();
                                                          }
                                                      }
                                                      ).Show());
            Observable.FromEventPattern<EventArgs>(OfficeToolStripButton, "Click")
                .Where(_ => !OfferRadioButton.Checked)
                .Subscribe(_ =>
                    new ResultForm(() =>
                    {
                        using (var package = new ExcelPackage(new FileInfo(Settings.Default.MarketFilePath)
                            .Open(FileMode.Open, FileAccess.Read, FileShare.ReadWrite)))
                        {
                            var sheet = package.Workbook.Worksheets["ТБК"];
                            return
                                sheet.Cells["B:B"]
                                .Where(cell => cell.Value != null && cell.Style.Fill.BackgroundColor.Rgb != "FFFF0000" && DataProcessing.NumberRegex.Match(cell.Address).Value != 1.ToString())
                                .Where(cell => cell.Offset(0, 13).Value == null || DataProcessing.OfficeRegex.Match(cell.Offset(0, 13).IfNotNull(c => c.Value).ToString()).Success)
                                .Where(cell => cell.Offset(0, 11).Value == null || DataProcessing.IsSquareMatches(cell.Offset(0, 11).IfNotNull(c => c.Value).ToString(), (int)squareNumericUpDown.Value, (int)variationNumericUpDown.Value))
                                .OrderBy(cell => cell.Offset(0, 10).IfNotNull(c => c.Value))
                                .Select(cell => new { cell, District = DataProcessing.GetDistrict(cell) })
                                .Where(cellInfo =>
                                    (SAOcheckBox.Checked ? cellInfo.District == "САО" : false)
                                    ||
                                    (SVAOcheckBox.Checked ? cellInfo.District == "СВАО" : false)
                                    ||
                                    (UZAOcheckBox.Checked ? cellInfo.District == "ЮЗАО" : false)
                                    ||
                                    (UVAOcheckBox.Checked ? cellInfo.District == "ЮВАО" : false)
                                    ||
                                    (UAOcheckBox.Checked ? cellInfo.District == "ЮАО" : false)
                                    ||
                                    (VAOcheckBox.Checked ? cellInfo.District == "ВАО" : false)
                                    ||
                                    (ZAOcheckBox.Checked ? cellInfo.District == "ЗАО" : false)
                                    ||
                                    (SZAOcheckBox.Checked ? cellInfo.District == "СЗАО" : false)
                                    ||
                                    (CAOcheckBox.Checked ? cellInfo.District == "ЦАО" : false)
                                    ||
                                    cellInfo.District == null
                                    )
                                .Select(cellInfo => new
                                {
                                    Name = cellInfo.cell.IfNotNull(c => c.Value),
                                    cellInfo.District,
                                    Metro = cellInfo.cell.Offset(0, 10).IfNotNull(c => c.Value),
                                    Area = cellInfo.cell.Offset(0, 11).IfNotNull(c => c.Value),
                                    Power = cellInfo.cell.Offset(0, 12).IfNotNull(c => c.Value),
                                    Function = cellInfo.cell.Offset(0, 13).IfNotNull(c => c.Value),
                                    Remarks = cellInfo.cell.Offset(0, 14).IfNotNull(c => c.Value),
                                })
                                    .ToList();
                        }
                    }).Show());
            Observable.FromEventPattern<EventArgs>(DiscoToolStripButton, "Click")
                .Where(_ => OfferRadioButton.Checked)
                .Subscribe(_ => new ResultForm(
                                                      () =>
                                                      {
                                                          using (var package = new ExcelPackage(new FileInfo(OfferRadioButton.Checked ? Settings.Default.OfferFilePath : Settings.Default.MarketFilePath)
                                                                                                    .Open(FileMode.Open, FileAccess.Read, FileShare.ReadWrite)))
                                                          {
                                                              var sheet = package.Workbook.Worksheets["ТБК"];
                                                              return
                                                                  sheet.Cells["C:C"]
                                                                  .Where(cell => cell.Value != null && cell.Style.Fill.BackgroundColor.Rgb != "FFFF0000" && DataProcessing.NumberRegex.Match(cell.Address).Value != 1.ToString())
                                                                  .Where(cell => cell.Offset(0, 13).Value == null || DataProcessing.DiscoRegex.Match(cell.Offset(0, 13).IfNotNull(c => c.Value).ToString()).Success)
                                                                  .Where(cell => cell.Offset(0, 11).Value == null || DataProcessing.IsSquareMatches(cell.Offset(0, 11).IfNotNull(c => c.Value).ToString(), (int)squareNumericUpDown.Value, (int)variationNumericUpDown.Value))
                                                                  .OrderBy(cell => cell.Offset(0, 10).IfNotNull(c => c.Value))
                                                                      .Select(cell => new { cell, District = DataProcessing.GetDistrict(cell) })
                                                                  .Where(cellInfo =>
                                                                      (SAOcheckBox.Checked ? cellInfo.District == "САО" : false)
                                                                      ||
                                                                      (SVAOcheckBox.Checked ? cellInfo.District == "СВАО" : false)
                                                                      ||
                                                                      (UZAOcheckBox.Checked ? cellInfo.District == "ЮЗАО" : false)
                                                                      ||
                                                                      (UVAOcheckBox.Checked ? cellInfo.District == "ЮВАО" : false)
                                                                      ||
                                                                      (UAOcheckBox.Checked ? cellInfo.District == "ЮАО" : false)
                                                                      ||
                                                                      (VAOcheckBox.Checked ? cellInfo.District == "ВАО" : false)
                                                                      ||
                                                                      (ZAOcheckBox.Checked ? cellInfo.District == "ЗАО" : false)
                                                                      ||
                                                                      (SZAOcheckBox.Checked ? cellInfo.District == "СЗАО" : false)
                                                                      ||
                                                                      (CAOcheckBox.Checked ? cellInfo.District == "ЦАО" : false)
                                                                      ||
                                                                      cellInfo.District == null
                                                                      )
                                                                      .Select(cellInfo => new
                                                                      {
                                                                          Name = cellInfo.cell.Offset(0, -1).IfNotNull(c => c.Value) ?? cellInfo.cell.Offset(0, -2).IfNotNull(c => c.Value),
                                                                          Contact = cellInfo.cell.IfNotNull(c => c.Value),
                                                                          cellInfo.District,
                                                                          Metro = cellInfo.cell.Offset(0, 10).IfNotNull(c => c.Value),
                                                                          Area = cellInfo.cell.Offset(0, 11).IfNotNull(c => c.Value),
                                                                          Function = cellInfo.cell.Offset(0, 13).IfNotNull(c => c.Value),
                                                                          Remarks = cellInfo.cell.Offset(0, 14).IfNotNull(c => c.Value),
                                                                      })
                                                                      .ToList();
                                                          }
                                                      }
                                                      ).Show());
            Observable.FromEventPattern<EventArgs>(DiscoToolStripButton, "Click")
                .Where(_ => !OfferRadioButton.Checked)
                .Subscribe(_ =>
                    new ResultForm(() =>
                    {
                        using (var package = new ExcelPackage(new FileInfo(Settings.Default.MarketFilePath)
                            .Open(FileMode.Open, FileAccess.Read, FileShare.ReadWrite)))
                        {
                            var sheet = package.Workbook.Worksheets["ТБК"];
                            return
                                sheet.Cells["B:B"]
                                .Where(cell => cell.Value != null && cell.Style.Fill.BackgroundColor.Rgb != "FFFF0000" && DataProcessing.NumberRegex.Match(cell.Address).Value != 1.ToString())
                                .Where(cell => cell.Offset(0, 13).Value == null || DataProcessing.DiscoRegex.Match(cell.Offset(0, 13).IfNotNull(c => c.Value).ToString()).Success)
                                .Where(cell => cell.Offset(0, 11).Value == null || DataProcessing.IsSquareMatches(cell.Offset(0, 11).IfNotNull(c => c.Value).ToString(), (int)squareNumericUpDown.Value, (int)variationNumericUpDown.Value))
                                .OrderBy(cell => cell.Offset(0, 10).IfNotNull(c => c.Value))
                                .Select(cell => new { cell, District = DataProcessing.GetDistrict(cell) })
                                .Where(cellInfo =>
                                    (SAOcheckBox.Checked ? cellInfo.District == "САО" : false)
                                    ||
                                    (SVAOcheckBox.Checked ? cellInfo.District == "СВАО" : false)
                                    ||
                                    (UZAOcheckBox.Checked ? cellInfo.District == "ЮЗАО" : false)
                                    ||
                                    (UVAOcheckBox.Checked ? cellInfo.District == "ЮВАО" : false)
                                    ||
                                    (UAOcheckBox.Checked ? cellInfo.District == "ЮАО" : false)
                                    ||
                                    (VAOcheckBox.Checked ? cellInfo.District == "ВАО" : false)
                                    ||
                                    (ZAOcheckBox.Checked ? cellInfo.District == "ЗАО" : false)
                                    ||
                                    (SZAOcheckBox.Checked ? cellInfo.District == "СЗАО" : false)
                                    ||
                                    (CAOcheckBox.Checked ? cellInfo.District == "ЦАО" : false)
                                    ||
                                    cellInfo.District == null
                                    )
                                .Select(cellInfo => new
                                {
                                    Name = cellInfo.cell.IfNotNull(c => c.Value),
                                    cellInfo.District,
                                    Metro = cellInfo.cell.Offset(0, 10).IfNotNull(c => c.Value),
                                    Area = cellInfo.cell.Offset(0, 11).IfNotNull(c => c.Value),
                                    Power = cellInfo.cell.Offset(0, 12).IfNotNull(c => c.Value),
                                    Function = cellInfo.cell.Offset(0, 13).IfNotNull(c => c.Value),
                                    Remarks = cellInfo.cell.Offset(0, 14).IfNotNull(c => c.Value),
                                })
                                    .ToList();
                        }
                    }).Show());
            Observable.FromEventPattern<EventArgs>(HouseKeepingToolStripButton, "Click")
                .Where(_ => OfferRadioButton.Checked)
                .Subscribe(_ => new ResultForm(
                                                      () =>
                                                      {
                                                          using (var package = new ExcelPackage(new FileInfo(OfferRadioButton.Checked ? Settings.Default.OfferFilePath : Settings.Default.MarketFilePath)
                                                                                                    .Open(FileMode.Open, FileAccess.Read, FileShare.ReadWrite)))
                                                          {
                                                              var sheet = package.Workbook.Worksheets["ТБК"];
                                                              return
                                                                  sheet.Cells["C:C"]
                                                                  .Where(cell => cell.Value != null && cell.Style.Fill.BackgroundColor.Rgb != "FFFF0000" && DataProcessing.NumberRegex.Match(cell.Address).Value != 1.ToString())
                                                                  .Where(cell => cell.Offset(0, 13).Value == null || DataProcessing.HouseKeepingRegex.Match(cell.Offset(0, 13).IfNotNull(c => c.Value).ToString()).Success)
                                                                  .Where(cell => cell.Offset(0, 11).Value == null || DataProcessing.IsSquareMatches(cell.Offset(0, 11).IfNotNull(c => c.Value).ToString(), (int)squareNumericUpDown.Value, (int)variationNumericUpDown.Value))
                                                                  .OrderBy(cell => cell.Offset(0, 10).IfNotNull(c => c.Value))
                                                                      .Select(cell => new { cell, District = DataProcessing.GetDistrict(cell) })
                                                                  .Where(cellInfo =>
                                                                      (SAOcheckBox.Checked ? cellInfo.District == "САО" : false)
                                                                      ||
                                                                      (SVAOcheckBox.Checked ? cellInfo.District == "СВАО" : false)
                                                                      ||
                                                                      (UZAOcheckBox.Checked ? cellInfo.District == "ЮЗАО" : false)
                                                                      ||
                                                                      (UVAOcheckBox.Checked ? cellInfo.District == "ЮВАО" : false)
                                                                      ||
                                                                      (UAOcheckBox.Checked ? cellInfo.District == "ЮАО" : false)
                                                                      ||
                                                                      (VAOcheckBox.Checked ? cellInfo.District == "ВАО" : false)
                                                                      ||
                                                                      (ZAOcheckBox.Checked ? cellInfo.District == "ЗАО" : false)
                                                                      ||
                                                                      (SZAOcheckBox.Checked ? cellInfo.District == "СЗАО" : false)
                                                                      ||
                                                                      (CAOcheckBox.Checked ? cellInfo.District == "ЦАО" : false)
                                                                      ||
                                                                      cellInfo.District == null
                                                                      )
                                                                      .Select(cellInfo => new
                                                                      {
                                                                          Name = cellInfo.cell.Offset(0, -1).IfNotNull(c => c.Value) ?? cellInfo.cell.Offset(0, -2).IfNotNull(c => c.Value),
                                                                          Contact = cellInfo.cell.IfNotNull(c => c.Value),
                                                                          cellInfo.District,
                                                                          Metro = cellInfo.cell.Offset(0, 10).IfNotNull(c => c.Value),
                                                                          Area = cellInfo.cell.Offset(0, 11).IfNotNull(c => c.Value),
                                                                          Function = cellInfo.cell.Offset(0, 13).IfNotNull(c => c.Value),
                                                                          Remarks = cellInfo.cell.Offset(0, 14).IfNotNull(c => c.Value),
                                                                      })
                                                                      .ToList();
                                                          }
                                                      }
                                                      ).Show());
            Observable.FromEventPattern<EventArgs>(HouseKeepingToolStripButton, "Click")
                .Where(_ => !OfferRadioButton.Checked)
                .Subscribe(_ =>
                    new ResultForm(() =>
                    {
                        using (var package = new ExcelPackage(new FileInfo(Settings.Default.MarketFilePath)
                            .Open(FileMode.Open, FileAccess.Read, FileShare.ReadWrite)))
                        {
                            var sheet = package.Workbook.Worksheets["ТБК"];
                            return
                                sheet.Cells["B:B"]
                                .Where(cell => cell.Value != null && cell.Style.Fill.BackgroundColor.Rgb != "FFFF0000" && DataProcessing.NumberRegex.Match(cell.Address).Value != 1.ToString())
                                .Where(cell => cell.Offset(0, 13).Value == null || DataProcessing.HouseKeepingRegex.Match(cell.Offset(0, 13).IfNotNull(c => c.Value).ToString()).Success)
                                .Where(cell => cell.Offset(0, 11).Value == null || DataProcessing.IsSquareMatches(cell.Offset(0, 11).IfNotNull(c => c.Value).ToString(), (int)squareNumericUpDown.Value, (int)variationNumericUpDown.Value))
                                .OrderBy(cell => cell.Offset(0, 10).IfNotNull(c => c.Value))
                                .Select(cell => new { cell, District = DataProcessing.GetDistrict(cell) })
                                .Where(cellInfo =>
                                    (SAOcheckBox.Checked ? cellInfo.District == "САО" : false)
                                    ||
                                    (SVAOcheckBox.Checked ? cellInfo.District == "СВАО" : false)
                                    ||
                                    (UZAOcheckBox.Checked ? cellInfo.District == "ЮЗАО" : false)
                                    ||
                                    (UVAOcheckBox.Checked ? cellInfo.District == "ЮВАО" : false)
                                    ||
                                    (UAOcheckBox.Checked ? cellInfo.District == "ЮАО" : false)
                                    ||
                                    (VAOcheckBox.Checked ? cellInfo.District == "ВАО" : false)
                                    ||
                                    (ZAOcheckBox.Checked ? cellInfo.District == "ЗАО" : false)
                                    ||
                                    (SZAOcheckBox.Checked ? cellInfo.District == "СЗАО" : false)
                                    ||
                                    (CAOcheckBox.Checked ? cellInfo.District == "ЦАО" : false)
                                    ||
                                    cellInfo.District == null
                                    )
                                .Select(cellInfo => new
                                {
                                    Name = cellInfo.cell.IfNotNull(c => c.Value),
                                    cellInfo.District,
                                    Metro = cellInfo.cell.Offset(0, 10).IfNotNull(c => c.Value),
                                    Area = cellInfo.cell.Offset(0, 11).IfNotNull(c => c.Value),
                                    Power = cellInfo.cell.Offset(0, 12).IfNotNull(c => c.Value),
                                    Function = cellInfo.cell.Offset(0, 13).IfNotNull(c => c.Value),
                                    Remarks = cellInfo.cell.Offset(0, 14).IfNotNull(c => c.Value),
                                })
                                    .ToList();
                        }
                    }).Show());
            Observable.FromEventPattern<EventArgs>(FactoryToolStripButton, "Click")
                .Where(_ => OfferRadioButton.Checked)
                .Subscribe(_ => new ResultForm(
                                                      () =>
                                                      {
                                                          using (var package = new ExcelPackage(new FileInfo(OfferRadioButton.Checked ? Settings.Default.OfferFilePath : Settings.Default.MarketFilePath)
                                                                                                    .Open(FileMode.Open, FileAccess.Read, FileShare.ReadWrite)))
                                                          {
                                                              var sheet = package.Workbook.Worksheets["ТБК"];
                                                              return
                                                                  sheet.Cells["C:C"]
                                                                  .Where(cell => cell.Value != null && cell.Style.Fill.BackgroundColor.Rgb != "FFFF0000" && DataProcessing.NumberRegex.Match(cell.Address).Value != 1.ToString())
                                                                  .Where(cell => cell.Offset(0, 13).Value == null || DataProcessing.FactoryRegex.Match(cell.Offset(0, 13).IfNotNull(c => c.Value).ToString()).Success)
                                                                  .Where(cell => cell.Offset(0, 11).Value == null || DataProcessing.IsSquareMatches(cell.Offset(0, 11).IfNotNull(c => c.Value).ToString(), (int)squareNumericUpDown.Value, (int)variationNumericUpDown.Value))
                                                                  .OrderBy(cell => cell.Offset(0, 10).IfNotNull(c => c.Value))
                                                                      .Select(cell => new { cell, District = DataProcessing.GetDistrict(cell) })
                                                                  .Where(cellInfo =>
                                                                      (SAOcheckBox.Checked ? cellInfo.District == "САО" : false)
                                                                      ||
                                                                      (SVAOcheckBox.Checked ? cellInfo.District == "СВАО" : false)
                                                                      ||
                                                                      (UZAOcheckBox.Checked ? cellInfo.District == "ЮЗАО" : false)
                                                                      ||
                                                                      (UVAOcheckBox.Checked ? cellInfo.District == "ЮВАО" : false)
                                                                      ||
                                                                      (UAOcheckBox.Checked ? cellInfo.District == "ЮАО" : false)
                                                                      ||
                                                                      (VAOcheckBox.Checked ? cellInfo.District == "ВАО" : false)
                                                                      ||
                                                                      (ZAOcheckBox.Checked ? cellInfo.District == "ЗАО" : false)
                                                                      ||
                                                                      (SZAOcheckBox.Checked ? cellInfo.District == "СЗАО" : false)
                                                                      ||
                                                                      (CAOcheckBox.Checked ? cellInfo.District == "ЦАО" : false)
                                                                      ||
                                                                      cellInfo.District == null
                                                                      )
                                                                      .Select(cellInfo => new
                                                                      {
                                                                          Name = cellInfo.cell.Offset(0, -1).IfNotNull(c => c.Value) ?? cellInfo.cell.Offset(0, -2).IfNotNull(c => c.Value),
                                                                          Contact = cellInfo.cell.IfNotNull(c => c.Value),
                                                                          cellInfo.District,
                                                                          Metro = cellInfo.cell.Offset(0, 10).IfNotNull(c => c.Value),
                                                                          Area = cellInfo.cell.Offset(0, 11).IfNotNull(c => c.Value),
                                                                          Function = cellInfo.cell.Offset(0, 13).IfNotNull(c => c.Value),
                                                                          Remarks = cellInfo.cell.Offset(0, 14).IfNotNull(c => c.Value),
                                                                      })
                                                                      .ToList();
                                                          }
                                                      }
                                                      ).Show());
            Observable.FromEventPattern<EventArgs>(FactoryToolStripButton, "Click")
                .Where(_ => !OfferRadioButton.Checked)
                .Subscribe(_ =>
                    new ResultForm(() =>
                    {
                        using (var package = new ExcelPackage(new FileInfo(Settings.Default.MarketFilePath)
                            .Open(FileMode.Open, FileAccess.Read, FileShare.ReadWrite)))
                        {
                            var sheet = package.Workbook.Worksheets["ТБК"];
                            return
                                sheet.Cells["B:B"]
                                .Where(cell => cell.Value != null && cell.Style.Fill.BackgroundColor.Rgb != "FFFF0000" && DataProcessing.NumberRegex.Match(cell.Address).Value != 1.ToString())
                                .Where(cell => cell.Offset(0, 13).Value == null || DataProcessing.FactoryRegex.Match(cell.Offset(0, 13).IfNotNull(c => c.Value).ToString()).Success)
                                .Where(cell => cell.Offset(0, 11).Value == null || DataProcessing.IsSquareMatches(cell.Offset(0, 11).IfNotNull(c => c.Value).ToString(), (int)squareNumericUpDown.Value, (int)variationNumericUpDown.Value))
                                .OrderBy(cell => cell.Offset(0, 10).IfNotNull(c => c.Value))
                                .Select(cell => new { cell, District = DataProcessing.GetDistrict(cell) })
                                .Where(cellInfo =>
                                    (SAOcheckBox.Checked ? cellInfo.District == "САО" : false)
                                    ||
                                    (SVAOcheckBox.Checked ? cellInfo.District == "СВАО" : false)
                                    ||
                                    (UZAOcheckBox.Checked ? cellInfo.District == "ЮЗАО" : false)
                                    ||
                                    (UVAOcheckBox.Checked ? cellInfo.District == "ЮВАО" : false)
                                    ||
                                    (UAOcheckBox.Checked ? cellInfo.District == "ЮАО" : false)
                                    ||
                                    (VAOcheckBox.Checked ? cellInfo.District == "ВАО" : false)
                                    ||
                                    (ZAOcheckBox.Checked ? cellInfo.District == "ЗАО" : false)
                                    ||
                                    (SZAOcheckBox.Checked ? cellInfo.District == "СЗАО" : false)
                                    ||
                                    (CAOcheckBox.Checked ? cellInfo.District == "ЦАО" : false)
                                    ||
                                    cellInfo.District == null
                                    )
                                .Select(cellInfo => new
                                {
                                    Name = cellInfo.cell.IfNotNull(c => c.Value),
                                    cellInfo.District,
                                    Metro = cellInfo.cell.Offset(0, 10).IfNotNull(c => c.Value),
                                    Area = cellInfo.cell.Offset(0, 11).IfNotNull(c => c.Value),
                                    Power = cellInfo.cell.Offset(0, 12).IfNotNull(c => c.Value),
                                    Function = cellInfo.cell.Offset(0, 13).IfNotNull(c => c.Value),
                                    Remarks = cellInfo.cell.Offset(0, 14).IfNotNull(c => c.Value),
                                })
                                    .ToList();
                        }
                    }).Show());
            Observable.FromEventPattern<EventArgs>(BeautyToolStripButton, "Click")
                .Where(_ => OfferRadioButton.Checked)
                .Subscribe(_ => new ResultForm(
                                                      () =>
                                                      {
                                                          using (var package = new ExcelPackage(new FileInfo(OfferRadioButton.Checked ? Settings.Default.OfferFilePath : Settings.Default.MarketFilePath)
                                                                                                    .Open(FileMode.Open, FileAccess.Read, FileShare.ReadWrite)))
                                                          {
                                                              var sheet = package.Workbook.Worksheets["ТБК"];
                                                              return
                                                                  sheet.Cells["C:C"]
                                                                  .Where(cell => cell.Value != null && cell.Style.Fill.BackgroundColor.Rgb != "FFFF0000" && DataProcessing.NumberRegex.Match(cell.Address).Value != 1.ToString())
                                                                  .Where(cell => cell.Offset(0, 13).Value == null || DataProcessing.BeautyRegex.Match(cell.Offset(0, 13).IfNotNull(c => c.Value).ToString()).Success)
                                                                  .Where(cell => cell.Offset(0, 11).Value == null || DataProcessing.IsSquareMatches(cell.Offset(0, 11).IfNotNull(c => c.Value).ToString(), (int)squareNumericUpDown.Value, (int)variationNumericUpDown.Value))
                                                                  .OrderBy(cell => cell.Offset(0, 10).IfNotNull(c => c.Value))
                                                                      .Select(cell => new { cell, District = DataProcessing.GetDistrict(cell) })
                                                                  .Where(cellInfo =>
                                                                      (SAOcheckBox.Checked ? cellInfo.District == "САО" : false)
                                                                      ||
                                                                      (SVAOcheckBox.Checked ? cellInfo.District == "СВАО" : false)
                                                                      ||
                                                                      (UZAOcheckBox.Checked ? cellInfo.District == "ЮЗАО" : false)
                                                                      ||
                                                                      (UVAOcheckBox.Checked ? cellInfo.District == "ЮВАО" : false)
                                                                      ||
                                                                      (UAOcheckBox.Checked ? cellInfo.District == "ЮАО" : false)
                                                                      ||
                                                                      (VAOcheckBox.Checked ? cellInfo.District == "ВАО" : false)
                                                                      ||
                                                                      (ZAOcheckBox.Checked ? cellInfo.District == "ЗАО" : false)
                                                                      ||
                                                                      (SZAOcheckBox.Checked ? cellInfo.District == "СЗАО" : false)
                                                                      ||
                                                                      (CAOcheckBox.Checked ? cellInfo.District == "ЦАО" : false)
                                                                      ||
                                                                      cellInfo.District == null
                                                                      )
                                                                      .Select(cellInfo => new
                                                                      {
                                                                          Name = cellInfo.cell.Offset(0, -1).IfNotNull(c => c.Value) ?? cellInfo.cell.Offset(0, -2).IfNotNull(c => c.Value),
                                                                          Contact = cellInfo.cell.IfNotNull(c => c.Value),
                                                                          cellInfo.District,
                                                                          Metro = cellInfo.cell.Offset(0, 10).IfNotNull(c => c.Value),
                                                                          Area = cellInfo.cell.Offset(0, 11).IfNotNull(c => c.Value),
                                                                          Function = cellInfo.cell.Offset(0, 13).IfNotNull(c => c.Value),
                                                                          Remarks = cellInfo.cell.Offset(0, 14).IfNotNull(c => c.Value),
                                                                      })
                                                                      .ToList();
                                                          }
                                                      }
                                                      ).Show());
            Observable.FromEventPattern<EventArgs>(BeautyToolStripButton, "Click")
                .Where(_ => !OfferRadioButton.Checked)
                .Subscribe(_ =>
                    new ResultForm(() =>
                    {
                        using (var package = new ExcelPackage(new FileInfo(Settings.Default.MarketFilePath)
                            .Open(FileMode.Open, FileAccess.Read, FileShare.ReadWrite)))
                        {
                            var sheet = package.Workbook.Worksheets["ТБК"];
                            return
                                sheet.Cells["B:B"]
                                .Where(cell => cell.Value != null && cell.Style.Fill.BackgroundColor.Rgb != "FFFF0000" && DataProcessing.NumberRegex.Match(cell.Address).Value != 1.ToString())
                                .Where(cell => cell.Offset(0, 13).Value == null || DataProcessing.BeautyRegex.Match(cell.Offset(0, 13).IfNotNull(c => c.Value).ToString()).Success)
                                .Where(cell => cell.Offset(0, 11).Value == null || DataProcessing.IsSquareMatches(cell.Offset(0, 11).IfNotNull(c => c.Value).ToString(), (int)squareNumericUpDown.Value, (int)variationNumericUpDown.Value))
                                .OrderBy(cell => cell.Offset(0, 10).IfNotNull(c => c.Value))
                                .Select(cell => new { cell, District = DataProcessing.GetDistrict(cell) })
                                .Where(cellInfo =>
                                    (SAOcheckBox.Checked ? cellInfo.District == "САО" : false)
                                    ||
                                    (SVAOcheckBox.Checked ? cellInfo.District == "СВАО" : false)
                                    ||
                                    (UZAOcheckBox.Checked ? cellInfo.District == "ЮЗАО" : false)
                                    ||
                                    (UVAOcheckBox.Checked ? cellInfo.District == "ЮВАО" : false)
                                    ||
                                    (UAOcheckBox.Checked ? cellInfo.District == "ЮАО" : false)
                                    ||
                                    (VAOcheckBox.Checked ? cellInfo.District == "ВАО" : false)
                                    ||
                                    (ZAOcheckBox.Checked ? cellInfo.District == "ЗАО" : false)
                                    ||
                                    (SZAOcheckBox.Checked ? cellInfo.District == "СЗАО" : false)
                                    ||
                                    (CAOcheckBox.Checked ? cellInfo.District == "ЦАО" : false)
                                    ||
                                    cellInfo.District == null
                                    )
                                .Select(cellInfo => new
                                {
                                    Name = cellInfo.cell.IfNotNull(c => c.Value),
                                    cellInfo.District,
                                    Metro = cellInfo.cell.Offset(0, 10).IfNotNull(c => c.Value),
                                    Area = cellInfo.cell.Offset(0, 11).IfNotNull(c => c.Value),
                                    Power = cellInfo.cell.Offset(0, 12).IfNotNull(c => c.Value),
                                    Function = cellInfo.cell.Offset(0, 13).IfNotNull(c => c.Value),
                                    Remarks = cellInfo.cell.Offset(0, 14).IfNotNull(c => c.Value),
                                })
                                    .ToList();
                        }
                    }).Show());

            

        }

        private readonly Subject<uint> _filesPathSet = new Subject<uint>();
    }
}
